<?php

require_once('tcpdf/config/lang/eng.php');
require_once('tcpdf/tcpdf.php');
include('connect_db.php');

// $act = $_GET[act];
// $date = $_GET[date];

class MYPDF extends TCPDF {

	//Page header
	public function Header() {

		// Title
		$ypage=10;
		$this->SetFont('helvetica', 'B', 12);
		$this->MultiCell('', 5, 'Lembongan Island - Bali', 0, 'L', 0, 0, '', $ypage, true); 
		$this->SetFont('helvetica', '', 10);
		$this->MultiCell('', 5, 'purinusa@hotmail.com +62 366 559 63 72', 0, 'L', 0, 0, '', $ypage+6, true);
		$style3 = array('width' => 0.3, 'cap' => 'round', 'join' => 'round', 'dash' => 0, 'color' => array(0, 0, 0));
		$this->Line(15, $ypage+14, 281, $ypage+14, $style3);
		$this->Line(15, $ypage+15, 281, $ypage+15, $style3);

		// Keterangan laporan
		$this->SetFont('helvetica', 'B', 10);
		$ypage2=28;

		if ($_GET[act] == "monthviewagent") {
			$sub_head = "Month Agent Comission Report";
			$exdate = explode("-",$_GET[date]);
			$month_name = array("January", "February", "March", "April", "Mei", "June", "July", "August", "September", "October", "November", "December");
			$month = $exdate[1];
			$display_date = $month_name[$month-1].", ".$exdate[0];
		}elseif ($_GET[act] == "yearviewagent") {
			$sub_head = "Year Agent Comission Report";
			$display_date = $_GET[date];
		}
		
		$this->MultiCell('', 5, $sub_head.' in '.$display_date, 0, 'L', 0, 0, '', $ypage2, true);
		$this->SetFont('helvetica', '', 10);
	}

	// Page footer
	public function Footer() {

		$style3 = array('width' => 0.5, 'cap' => 'round', 'join' => 'round', 'dash' => 0, 'color' => array(0, 0, 0));
		$this->Line(10, 285, 200, 285, $style3);
		// Position at 10 mm from bottom
		$this->SetY(-10);
		// Set font
		$this->SetFont('helvetica', 'I', 8);
		// Page number
		$this->Cell(0, 7, 'Page '.$this->getAliasNumPage().' of '.$this->getAliasNbPages(), 0, false, 'C', 0, '', 0, false, 'T', 'M');
		$style = array(
			'position' => '',
			'align' => 'L',
			'stretch' => false,
			'fitwidth' => false,
			'cellfitalign' => '',
			'border' => false,
			'hpadding' => '',
			'vpadding' => '',
			'fgcolor' => array(0,0,0),
			'bgcolor' => false, //array(255,255,255),
			'text' => true,
			'font' => 'helvetica',
			'fontsize' => 6,
			'stretchtext' => 0
		);
	}
}

// create new PDF document
$pdf = new MYPDF('L', 'mm', 'A4', true, 'UTF-8', false);
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('Lembongan Island');
$pdf->SetTitle('Report');
$pdf->SetSubject('Agent Report');
$pdf->SetKeywords('TCPDF, PDF, example, test, guide');

// set font
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
$pdf->SetAutoPageBreak(false, PDF_MARGIN_BOTTOM);
$pdf->SetFont('helvetica', '', 12);

$pdf->AddPage();

$ypage2=8;

$pdf->SetFont('helvetica', '', 9);

	if ($_GET[act] == "monthviewagent") {

		$html = '
			<table border="1" cellpadding="3">
				<thead>
			    <tr>
			      <td style="vertical-align: middle; text-align: center; width:30px;">No.</td>
			      <td style="vertical-align: middle; text-align: center; width:160px;">Agent Name</td>
			      <td style="vertical-align: middle; text-align: center; width:140px;">Customer Name</td>
			      <td style="vertical-align: middle; text-align: center; width:100px;">Room</td>
			      <td style="vertical-align: middle; text-align: center; width:120px;">Room Paid (IDR)</td>
			      <td style="vertical-align: middle; text-align: center; width:80px;">Comission Status</td>
			      <td style="vertical-align: middle; text-align: center; width:124px;">Agent Comission (IDR)</td>
			    </tr>
			  </thead>

			  <tbody>';
					$grand_total = 0;
	      	$hitungBaris = 1;
							
					$sql_list_date = "SELECT DATE_FORMAT(a.checkout, '%Y-%c-%d') AS 'date_checkout', 
					DATE_FORMAT(a.checkout, '%M %d, %Y') AS 'date_tampil'
					FROM front_office a 
					WHERE a.is_paid = 1 AND CONCAT(YEAR(a.checkout),'-',MONTH(a.checkout)) = '".$_GET[date]."' AND a.idagent != ''
					GROUP BY DATE_FORMAT(a.checkout, '%Y-%c-%d') ASC";
					$query_list_date = mysql_query($sql_list_date);
					$count_list_date = mysql_num_rows($query_list_date);
					if ($count_list_date > 0) {
						$query_list_date = mysql_query($sql_list_date);
						$i = 1;
						while ($baris_list_date = mysql_fetch_array($query_list_date)) {
							$sql_report = "SELECT * FROM front_office a 
							WHERE a.is_paid = 1 AND a.idagent != '' AND 
							DATE_FORMAT(a.checkout, '%Y-%c-%d') = '".$baris_list_date['date_checkout']."' 
							GROUP BY a.idagent ASC";
							$query_report = mysql_query($sql_report);
							if (mysql_num_rows($query_report) > 0) {
								$hitungBaris++;
								if($hitungBaris >= 25){
								$html = $html.'</tbody></table>';
								$pdf->writeHTMLCell(0, 0, '14', $ypage2+28, $html, 0, 1, 0, true, 'L', true);
								$pdf->AddPage();
								$hitungBaris = 1;
								$html = '
								<table border="1" cellpadding="3">
									<thead>
								    <tr>
								      <td style="vertical-align: middle; text-align: center; width:30px;">No.</td>
								      <td style="vertical-align: middle; text-align: center; width:160px;">Agent Name</td>
								      <td style="vertical-align: middle; text-align: center; width:140px;">Customer Name</td>
								      <td style="vertical-align: middle; text-align: center; width:100px;">Room</td>
								      <td style="vertical-align: middle; text-align: center; width:120px;">Room Paid (IDR)</td>
								      <td style="vertical-align: middle; text-align: center; width:80px;">Comission Status</td>
								      <td style="vertical-align: middle; text-align: center; width:124px;">Agent Comission (IDR)</td>
								    </tr>
								  </thead>

								  <tbody>
								  <tr>
										<td colspan="7" style="background-color: #ccc; font-weight:bold;">'.$baris_list_date[date_tampil].'</td>
									</tr>
								';
								}else{
									$html=$html.'
									<tr>
										<td colspan="7" style="background-color: #ccc; font-weight:bold;">'.$baris_list_date[date_tampil].'</td>
									</tr>
									';
								}
								while ($baris_report = mysql_fetch_array($query_report)) {
									$sub_grand_total = 0;
									$sql_agent_service = "SELECT a.*, (DATE(a.checkout)-DATE(a.checkin)) as 'lama' FROM front_office a 
									WHERE a.is_paid = 1 AND DATE(a.checkout) = '".$baris_list_date['date_checkout']."' 
									AND a.idagent = '".$baris_report['idagent']."'";
									$query_agent_service = mysql_query($sql_agent_service);
									$count_agent_service = mysql_num_rows($query_agent_service);

									$sql_agent = "SELECT * FROM agent WHERE id = '".$baris_report['idagent']."'";
									$query_agent = mysql_query($sql_agent);
									$result_agent = mysql_fetch_array($query_agent);

									$query_agent_service = mysql_query($sql_agent_service);
									$urutan_baris = 1;
									$cekTampilHeader = 0;
									while ($baris_agent_service = mysql_fetch_array($query_agent_service)) {
										$sql_detail_room = "SELECT * FROM room WHERE id = '".$baris_agent_service[idroom]."'";
										$query_detail_room = mysql_query($sql_detail_room);
										$data_room = mysql_fetch_array($query_detail_room);

										$price_kamar = $baris_agent_service['real_paid'] * $baris_agent_service['lama'];

										$price_agent_service = (($price_kamar) * $result_agent['komisi'] / 100);

										$sql_status_paid = "SELECT is_paid, comission FROM agent_paid WHERE id_fo = '".$baris_agent_service[id]."' AND id_agent = '".$baris_report['idagent']."'";
										$query_status_paid = mysql_query($sql_status_paid);
										$status_paid = mysql_fetch_array($query_status_paid);

										if ($status_paid[comission] == 1) {
											$paid_for_agent = "Paid";
										}else{
											$paid_for_agent = "Unpaid";
										}

										if($hitungBaris > 24){
											$html = $html.'</tbody></table>';
											$pdf->writeHTMLCell(0, 0, '14', $ypage2+28, $html, 0, 1, 0, true, 'L', true);
											$pdf->AddPage();
											$hitungBaris = 2;
											$cekTampilHeader = 1;
											$html = '
											<table border="1" cellpadding="3">
												<thead>
											    <tr>
											      <td style="vertical-align: middle; text-align: center; width:30px;">No.</td>
											      <td style="vertical-align: middle; text-align: center; width:160px;">Agent Name</td>
											      <td style="vertical-align: middle; text-align: center; width:140px;">Customer Name</td>
											      <td style="vertical-align: middle; text-align: center; width:100px;">Room</td>
											      <td style="vertical-align: middle; text-align: center; width:120px;">Room Paid (IDR)</td>
											      <td style="vertical-align: middle; text-align: center; width:80px;">Comission Status</td>
											      <td style="vertical-align: middle; text-align: center; width:124px;">Agent Comission (IDR)</td>
											    </tr>
											  </thead>

											  <tbody>
										  ';

										  if ($urutan_baris < $count_agent_service) {
												$html=$html.'
												<tr>
													<td style="vertical-align: middle; text-align: center; width:30px;" rowspan="'.(($count_agent_service-$urutan_baris)+2).'"></td>
													<td style="vertical-align: middle; text-align: left; width:160px;" rowspan="'.(($count_agent_service-$urutan_baris)+2).'"></td>
													<td style="vertical-align: middle; text-align: left; width:140px;">'.$baris_agent_service[nama].'</td>
													<td style="vertical-align: middle; text-align: left; width:100px;">'.$data_room[nama].'</td>
													<td style="vertical-align: middle; text-align: right; width:120px;">'.number_format($price_kamar,2,",",".").'</td>
													<td style="vertical-align: middle; text-align: center; width:80px;">'.$paid_for_agent.'</td>
													<td style="vertical-align: middle; text-align: right; width:124px;">'.number_format($status_paid[comission],2,",",".").'</td>
												</tr>
												';
												$urutan_baris++;
										  }
										}

										if ($cekTampilHeader == 0) {
											if ($urutan_baris <= 1) {
												$html=$html.'
												<tr>
													<td style="vertical-align: middle; text-align: center; width:30px;" rowspan="'.($count_agent_service+1).'">'.$i.'</td>
													<td style="vertical-align: middle; text-align: left; width:160px;" rowspan="'.($count_agent_service+1).'">'.$result_agent[nama].'</td>
													<td style="vertical-align: middle; text-align: left; width:140px;">'.$baris_agent_service[nama].'</td>
													<td style="vertical-align: middle; text-align: left; width:100px;">'.$data_room[nama].'</td>
													<td style="vertical-align: middle; text-align: right; width:120px;">'.number_format($price_kamar,2,",",".").'</td>
													<td style="vertical-align: middle; text-align: center; width:80px;">'.$paid_for_agent.'</td>
													<td style="vertical-align: middle; text-align: right; width:124px;">'.number_format($status_paid[comission],2,",",".").'</td>
												</tr>
												';
											}else{
												$html=$html.'
												<tr>
													<td style="vertical-align: middle; text-align: left; width:140px;">'.$baris_agent_service[nama].'</td>
													<td style="vertical-align: middle; text-align: left; width:100px;">'.$data_room[nama].'</td>
													<td style="vertical-align: middle; text-align: right; width:120px;">'.number_format($price_kamar,2,",",".").'</td>
													<td style="vertical-align: middle; text-align: center; width:80px;">'.$paid_for_agent.'</td>
													<td style="vertical-align: middle; text-align: right; width:124px;">'.number_format($status_paid[comission],2,",",".").'</td>
												</tr>
												';
											}
											$sub_grand_total = $sub_grand_total+$status_paid[comission];
											$urutan_baris++;
											$hitungBaris++;
										}
										$cekTampilHeader = 0;
									}

									$html=$html.'
									<tr style="background-color: #eee;">
										<td style="vertical-align: middle; text-align: right;" colspan="4">Amount</td>
										<td style="vertical-align: middle; text-align: right;">'.number_format($sub_grand_total,2,",",".").'</td>
									</tr>
									';
									$hitungBaris++;
									$grand_total = $grand_total+$sub_grand_total;
									$i++;

									if($hitungBaris > 25){
										$html = $html.'</tbody></table>';
										$pdf->writeHTMLCell(0, 0, '14', $ypage2+28, $html, 0, 1, 0, true, 'L', true);
										$pdf->AddPage();
										$hitungBaris = 0;
										$html = '
										<table border="1" cellpadding="3">
											<thead>
										    <tr>
										      <td style="vertical-align: middle; text-align: center; width:30px;">No.</td>
										      <td style="vertical-align: middle; text-align: center; width:160px;">Agent Name</td>
										      <td style="vertical-align: middle; text-align: center; width:140px;">Customer Name</td>
										      <td style="vertical-align: middle; text-align: center; width:100px;">Room</td>
										      <td style="vertical-align: middle; text-align: center; width:120px;">Room Paid (IDR)</td>
										      <td style="vertical-align: middle; text-align: center; width:80px;">Comission Status</td>
										      <td style="vertical-align: middle; text-align: center; width:124px;">Agent Comission (IDR)</td>
										    </tr>
										  </thead>

										  <tbody>
									  ';
									}
								}
							}
						}

						$html=$html.'
						<tr style="background-color: #ccc; font-weight:bold;">
							<td colspan="6" style="vertical-align: middle; text-align: right;">Total Amount</td>
							<td style="vertical-align: middle; text-align: right;">'.number_format($grand_total,2,",",".").'</td>
						</tr>
						';
					}

				$html = $html.'
				</tbody>
			</table>
		';
		// end if month act
	}elseif ($_GET[act] == "yearviewagent") {
		
		$html = '
			<table border="1" cellpadding="3">
				<thead>
			    <tr>
			      <td style="vertical-align: middle; text-align: center; width:30px;">No.</td>
			      <td style="vertical-align: middle; text-align: center; width:160px;">Agent Name</td>
			      <td style="vertical-align: middle; text-align: center; width:140px;">Customer Name</td>
			      <td style="vertical-align: middle; text-align: center; width:100px;">Room</td>
			      <td style="vertical-align: middle; text-align: center; width:120px;">Room Paid (IDR)</td>
			      <td style="vertical-align: middle; text-align: center; width:80px;">Comission Status</td>
			      <td style="vertical-align: middle; text-align: center; width:124px;">Agent Comission (IDR)</td>
			    </tr>
			  </thead>

			  <tbody>';
					$grand_total = 0;
	      	$hitungBaris = 1;
							
					$sql_list_date = "SELECT DATE_FORMAT(a.checkout, '%Y-%c-%d') AS 'date_checkout', 
					DATE_FORMAT(a.checkout, '%M %d, %Y') AS 'date_tampil'
					FROM front_office a 
					WHERE a.is_paid = 1 AND YEAR(a.checkout) = '".$_GET[date]."' AND a.idagent != ''
					GROUP BY DATE_FORMAT(a.checkout, '%Y-%c-%d') ASC";
					$query_list_date = mysql_query($sql_list_date);
					$count_list_date = mysql_num_rows($query_list_date);
					if ($count_list_date > 0) {
						$query_list_date = mysql_query($sql_list_date);
						$i = 1;
						while ($baris_list_date = mysql_fetch_array($query_list_date)) {
							$sql_report = "SELECT * FROM front_office a 
							WHERE a.is_paid = 1 AND a.idagent != '' AND 
							DATE_FORMAT(a.checkout, '%Y-%c-%d') = '".$baris_list_date['date_checkout']."' 
							GROUP BY a.idagent ASC";
							$query_report = mysql_query($sql_report);
							if (mysql_num_rows($query_report) > 0) {
								$html=$html.'
								<tr>
									<td colspan="7" style="background-color: #ccc; font-weight:bold;">'.$baris_list_date[date_tampil].'</td>
								</tr>
								';
								$hitungBaris++;
								while ($baris_report = mysql_fetch_array($query_report)) {
									$sub_grand_total = 0;
									$sql_agent_service = "SELECT a.*, (DATE(a.checkout)-DATE(a.checkin)) as 'lama' FROM front_office a 
									WHERE a.is_paid = 1 AND DATE(a.checkout) = '".$baris_list_date['date_checkout']."' 
									AND a.idagent = '".$baris_report['idagent']."'";
									$query_agent_service = mysql_query($sql_agent_service);
									$count_agent_service = mysql_num_rows($query_agent_service);

									$sql_agent = "SELECT * FROM agent WHERE id = '".$baris_report['idagent']."'";
									$query_agent = mysql_query($sql_agent);
									$result_agent = mysql_fetch_array($query_agent);

									$query_agent_service = mysql_query($sql_agent_service);
									$urutan_baris = 1;
									$cekTampilHeader = 0;
									while ($baris_agent_service = mysql_fetch_array($query_agent_service)) {
										$sql_detail_room = "SELECT * FROM room WHERE id = '".$baris_agent_service[idroom]."'";
										$query_detail_room = mysql_query($sql_detail_room);

										$data_room = mysql_fetch_array($query_detail_room);

										$price_kamar = $baris_agent_service['real_paid'] * $baris_agent_service['lama'];
										$status_paid[comission] = (($price_kamar) * $result_agent['komisi'] / 100);

										$sql_status_paid = "SELECT is_paid, comission FROM agent_paid WHERE id_fo = '".$baris_agent_service[id]."' AND id_agent = '".$baris_report['idagent']."'";
										$query_status_paid = mysql_query($sql_status_paid);
										$status_paid = mysql_fetch_array($query_status_paid);

										if ($status_paid[comission] == 1) {
											$paid_for_agent = "Paid";
										}else{
											$paid_for_agent = "Unpaid";
										}

										if($hitungBaris > 25){
											$html = $html.'</tbody></table>';
											$pdf->writeHTMLCell(0, 0, '14', $ypage2+28, $html, 0, 1, 0, true, 'L', true);
											$pdf->AddPage();
											$hitungBaris = 2;
											$cekTampilHeader = 1;
											$html = '
											<table border="1" cellpadding="3">
												<thead>
											    <tr>
											      <td style="vertical-align: middle; text-align: center; width:30px;">No.</td>
											      <td style="vertical-align: middle; text-align: center; width:160px;">Agent Name</td>
											      <td style="vertical-align: middle; text-align: center; width:140px;">Customer Name</td>
											      <td style="vertical-align: middle; text-align: center; width:100px;">Room</td>
											      <td style="vertical-align: middle; text-align: center; width:120px;">Room Paid (IDR)</td>
											      <td style="vertical-align: middle; text-align: center; width:80px;">Comission Status</td>
											      <td style="vertical-align: middle; text-align: center; width:124px;">Agent Comission (IDR)</td>
											    </tr>
											  </thead>

											  <tbody>
										  ';

										  if ($urutan_baris < $count_agent_service) {
												$html=$html.'
												<tr>
													<td style="vertical-align: middle; text-align: center; width:30px;" rowspan="'.(($count_agent_service-$urutan_baris)+2).'"></td>
													<td style="vertical-align: middle; text-align: left; width:160px;" rowspan="'.(($count_agent_service-$urutan_baris)+2).'"></td>
													<td style="vertical-align: middle; text-align: left; width:140px;">'.$baris_agent_service[nama].'</td>
													<td style="vertical-align: middle; text-align: left; width:100px;">'.$data_room[nama].'</td>
													<td style="vertical-align: middle; text-align: right; width:120px;">'.number_format($price_kamar,2,",",".").'</td>
													<td style="vertical-align: middle; text-align: center; width:80px;">'.$paid_for_agent.'</td>
													<td style="vertical-align: middle; text-align: right; width:124px;">'.number_format($status_paid[comission],2,",",".").'</td>
												</tr>
												';
												$urutan_baris++;
										  }
										}

										if ($cekTampilHeader == 0) {
											if ($urutan_baris <= 1) {
												$html=$html.'
												<tr>
													<td style="vertical-align: middle; text-align: center; width:30px;" rowspan="'.($count_agent_service+1).'">'.$i.'</td>
													<td style="vertical-align: middle; text-align: left; width:160px;" rowspan="'.($count_agent_service+1).'">'.$result_agent[nama].'</td>
													<td style="vertical-align: middle; text-align: left; width:140px;">'.$baris_agent_service[nama].'</td>
													<td style="vertical-align: middle; text-align: left; width:100px;">'.$data_room[nama].'</td>
													<td style="vertical-align: middle; text-align: right; width:120px;">'.number_format($price_kamar,2,",",".").'</td>
													<td style="vertical-align: middle; text-align: center; width:80px;">'.$paid_for_agent.'</td>
													<td style="vertical-align: middle; text-align: right; width:124px;">'.number_format($status_paid[comission],2,",",".").'</td>
												</tr>
												';
											}else{
												$html=$html.'
												<tr>
													<td style="vertical-align: middle; text-align: left; width:140px;">'.$baris_agent_service[nama].'</td>
													<td style="vertical-align: middle; text-align: left; width:100px;">'.$data_room[nama].'</td>
													<td style="vertical-align: middle; text-align: right; width:120px;">'.number_format($price_kamar,2,",",".").'</td>
													<td style="vertical-align: middle; text-align: center; width:80px;">'.$paid_for_agent.'</td>
													<td style="vertical-align: middle; text-align: right; width:124px;">'.number_format($status_paid[comission],2,",",".").'</td>
												</tr>
												';
											}
											$sub_grand_total = $sub_grand_total+$status_paid[comission];
											$urutan_baris++;
											$hitungBaris++;
										}
										$cekTampilHeader = 0;
									}

									$html=$html.'
									<tr style="background-color: #eee;">
										<td style="vertical-align: middle; text-align: right;" colspan="4">Amount</td>
										<td style="vertical-align: middle; text-align: right;">'.number_format($sub_grand_total,2,",",".").'</td>
									</tr>
									';
									$hitungBaris++;
									$grand_total = $grand_total+$sub_grand_total;
									$i++;

									if($hitungBaris > 25){
										$html = $html.'</tbody></table>';
										$pdf->writeHTMLCell(0, 0, '14', $ypage2+28, $html, 0, 1, 0, true, 'L', true);
										$pdf->AddPage();
										$hitungBaris = 0;
										$html = '
										<table border="1" cellpadding="3">
											<thead>
										    <tr>
										      <td style="vertical-align: middle; text-align: center; width:30px;">No.</td>
										      <td style="vertical-align: middle; text-align: center; width:160px;">Agent Name</td>
										      <td style="vertical-align: middle; text-align: center; width:140px;">Customer Name</td>
										      <td style="vertical-align: middle; text-align: center; width:100px;">Room</td>
										      <td style="vertical-align: middle; text-align: center; width:120px;">Room Paid (IDR)</td>
										      <td style="vertical-align: middle; text-align: center; width:80px;">Comission Status</td>
										      <td style="vertical-align: middle; text-align: center; width:124px;">Agent Comission (IDR)</td>
										    </tr>
										  </thead>

										  <tbody>
									  ';
									}
								}
							}
						}

						$html=$html.'
						<tr style="background-color: #ccc; font-weight:bold;">
							<td colspan="6" style="vertical-align: middle; text-align: right;">Total Amount</td>
							<td style="vertical-align: middle; text-align: right;">'.number_format($grand_total,2,",",".").'</td>
						</tr>
						';
					}

				$html = $html.'
				</tbody>
			</table>
		';
		//---end if year act
	} 

$pdf->writeHTMLCell(0, 0, '14', $ypage2+28, $html, 0, 1, 0, true, 'L', true);

//Close and output PDF document
$pdf->Output('Lembongan-Island-'.$_GET[act].'_'.$_GET[date].'.pdf', 'I');

?>